﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

using Lenovo.CFI.Common;

namespace Lenovo.CFI.DAL
{
    public class IdeaDa
    {
        #region Idea

        public static List<Idea> GetIdea(int? belongTopicID, string title, string owner, 
            int? teamID, string teamManager, string teamVP,
            string submitType, IdeaStatus? status, List<int> multiStatus, string approver, 
            string finalType, string finalNextAction, bool? finalNextIP,
            int? finalActionStatus, int? finalIPStatus)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdea",
                belongTopicID, title, owner, teamID, teamManager, teamVP,
                submitType, (int?)status, InHelper.ConvertToInStrForSplit(multiStatus, false), approver,
                finalType, finalNextAction, finalNextIP, (int?)finalActionStatus, (int?)finalIPStatus);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }


        public static List<Idea> GetIdeaByBelongTopic(int belongTopicID)
        {
            return GetIdea(belongTopicID, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
        }

        public static List<Idea> GetIdeaByScheduleTopic(int scheduleTopicID)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaForSchedule", scheduleTopicID);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }

        public static List<Idea> GetIdeaForReview(int reviewTopicID, int? belongTopicID, 
            string title, string owner, int? teamID, string teamManager, string teamVP,
            string submitType, IdeaStatus? status, List<int> multiStatus,
            string reviewType, string reviewDcOwner, string reviewIPOwner,
            string reviewNextAction, bool? reviewNextIP,
            string reiviewActionExecutor, string reviewIPExecutor,
            int? reviewActionStatus, int? reviewIPStatus)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaForReview", reviewTopicID, belongTopicID, 
                title, owner, teamID, teamManager, teamVP,
                submitType, (int?)status, InHelper.ConvertToInStrForSplit(multiStatus, false),
                reviewType, reviewDcOwner, reviewIPOwner, reviewNextAction, reviewNextIP,
                reiviewActionExecutor, reviewIPExecutor, reviewActionStatus, reviewIPStatus);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }

        public static List<Idea> GetIdeaForReview(int reviewTopicID)
        {
            return GetIdeaForReview(reviewTopicID, null, null, null, null, null, null, null, null, null, 
                null, null, null, null, null, null, null, null, null);
        }

        public static List<Idea> GetIdeaByScheduleTopicForAdd(int scheduleTopicID)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaForScheduleAdd", scheduleTopicID);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }






        public static Idea GetByID(int id)
        {
            return GetByID(id, DBHelper.GetDatabase(), null);
        }
        public static Idea GetByID(int id, TranscationHelper trans)
        {
            return GetByID(id, trans.DataBase, trans.Transaction);
        }
        public static Idea GetByID(int id, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaByID", id);
            IDataReader dataReader;
            if (trans == null)
                dataReader = db.ExecuteReader(dbCommand);
            else
                dataReader = db.ExecuteReader(dbCommand, trans);

            using (dataReader)
            {
                if (dataReader.Read())
                {
                    return PopulateIdea(dataReader);
                }
            }

            return null;
        }

        public static List<Idea> GetByIDs(List<int> ids)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaByIDs", InHelper.ConvertToInStr(ids));

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }


        public static List<Idea> SearchIdea(int? belongTopicID,
            string title, string owner, int? ideaTeamID, int? ownerCurrentTeamID,
            string ideaTeamManager, string ownerCurrentManager, string ideaCurrentTeamManager,
            string ideaTeamVP, string ownerCurrentTeamVP, string ideaCurrentTeamVP,
            string submitType, List<string> relatedTypes, List<string> keyworkds,
            IdeaStatus? status, List<int> multiStatus, bool? searchable, DateTime? submitTimeF, DateTime? submitTimeT,
            DateTime? topicTimeF, DateTime? topicTimeT, string finalType, List<string> multiFinalTypes, string finalAction, bool? finalIP, 
            int? finalActionStatus, int? finalIPStatus, int? finalAward,
            string user)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaSearch",
                belongTopicID, title, owner, ideaTeamID, ownerCurrentTeamID,
                ideaTeamManager, ownerCurrentManager, ideaCurrentTeamManager, ideaTeamVP, ownerCurrentTeamVP, ideaCurrentTeamVP, submitType,
                InHelper.ConvertToInStrForSplit(relatedTypes, false), (relatedTypes == null ? 0 : relatedTypes.Count),
                InHelper.ConvertToInStrForSplit(keyworkds, false), (keyworkds == null ? 0 : keyworkds.Count),
                (int?)status, InHelper.ConvertToInStrForSplit(multiStatus, false),
                searchable, submitTimeF, submitTimeT, topicTimeF, topicTimeT,
                finalType, InHelper.ConvertToInStrForSplit(multiFinalTypes, false),
                finalAction, finalIP, finalActionStatus, finalIPStatus, finalAward,
                user);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }


        internal static Idea PopulateIdea(IDataReader dataReader)
        {
            Idea idea = new Idea();
            idea.ID = dataReader.GetInt32(0);
            idea.TopicID = dataReader.GetInt32(1);
            idea.No = dataReader.GetInt32(2);
            idea.Title = dataReader.GetString(3);
            idea.Type = dataReader.GetString(4);
            if (!dataReader.IsDBNull(5)) idea.RelatedType = new CodeList(dataReader.GetString(5));
            if (!dataReader.IsDBNull(6)) idea.Keywords = new CodeList(dataReader.GetString(6));
            if (!dataReader.IsDBNull(7)) idea.Freewords = new CodeList(dataReader.GetString(7));
            idea.Backgroud = dataReader.GetString(8);
            idea.Description = dataReader.GetString(9);
            idea.Values = dataReader.GetString(10);
            idea.PptFileID = dataReader.IsDBNull(11) ? (Guid?)null : dataReader.GetGuid(11);
            idea.Pic1FileID = dataReader.IsDBNull(12) ? (Guid?)null : dataReader.GetGuid(12);
            idea.Pic2FileID = dataReader.IsDBNull(13) ? (Guid?)null : dataReader.GetGuid(13);
            idea.Pic3FileID = dataReader.IsDBNull(14) ? (Guid?)null : dataReader.GetGuid(14);
            idea.Owner = dataReader.GetString(15);
            idea.TeamID = dataReader.GetInt32(16);
            idea.TeamManager = dataReader.GetString(17);
            idea.TeamVP = dataReader.GetString(18);
            idea.Status = (IdeaStatus)dataReader.GetInt32(19);
            idea.SubmitTime = dataReader.GetDateTime(20);
            idea.Approver = dataReader.IsDBNull(21) ? null : dataReader.GetString(21);
            idea.ApproverComment = dataReader.IsDBNull(22) ? null : dataReader.GetString(22);
            idea.DcComment = dataReader.IsDBNull(23) ? null : dataReader.GetString(23);
            idea.DcAttendMeeting = dataReader.IsDBNull(24) ? (bool?)null : dataReader.GetBoolean(24);
            idea.SecretLevel = dataReader.GetInt32(25);
            idea.CanSearch = dataReader.GetBoolean(26);
            idea.FinalReviewTopicID = dataReader.IsDBNull(27) ? (int?)null : dataReader.GetInt32(27);
            idea.FinalReviewID = dataReader.IsDBNull(28) ? (int?)null : dataReader.GetInt32(28);
            idea.FinalAward = dataReader.IsDBNull(29) ? (int?)null : dataReader.GetInt32(29);
            idea.FinalType = dataReader.IsDBNull(30) ? null : dataReader.GetString(30);
            idea.FinalIP = dataReader.IsDBNull(31) ? (bool?)null : dataReader.GetBoolean(31);
            idea.FinalAction = dataReader.IsDBNull(32) ? null : dataReader.GetString(32);
            idea.FinalActionBU = dataReader.IsDBNull(33) ? null : dataReader.GetString(33);
            idea.FinalActionStatus = (ExecutionStatus)dataReader.GetInt32(34);
            idea.FinalIPStatus = (ExecutionStatus)dataReader.GetInt32(35);

            return idea;
        }

        /// <summary>
        /// 插入时
        /// </summary>
        /// <param name="idea"></param>
        public static void InsertIdea(Idea idea)
        {
            InsertIdea(idea, DBHelper.GetDatabase(), null);
        }
        public static void InsertIdea(Idea idea, TranscationHelper trans)
        {
            InsertIdea(idea, trans.DataBase, trans.Transaction);
        }
        private static void InsertIdea(Idea idea, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertIdea",
                idea.TopicID, idea.No, idea.Title, idea.Type, idea.RelatedTypeText, idea.KeywordsText,
                idea.FreewordsText, idea.Backgroud, idea.Description, idea.Values, idea.PptFileID,
                idea.Pic1FileID, idea.Pic2FileID, idea.Pic3FileID, idea.Owner, idea.TeamID,
                idea.TeamManager, idea.TeamVP, (int)idea.Status, idea.SubmitTime, idea.Approver);

            try
            {
                if (trans == null)
                    idea.ID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
                else
                    idea.ID = Convert.ToInt32(db.ExecuteScalar(dbCommand, trans));
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void UpdateIdea(Idea idea)
        {
            UpdateIdea(idea, DBHelper.GetDatabase(), null);
        }
        public static void UpdateIdea(Idea idea, TranscationHelper trans)
        {
            UpdateIdea(idea, trans.DataBase, trans.Transaction);
        }
        private static void UpdateIdea(Idea idea, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_UpdateIdea", idea.ID, 
                idea.No, idea.Title, idea.Type, idea.RelatedTypeText, idea.KeywordsText, 
                idea.FreewordsText, idea.Backgroud, idea.Description, idea.Values, idea.PptFileID, 
                idea.Pic1FileID, idea.Pic2FileID, idea.Pic3FileID, idea.Owner, idea.TeamID,
                idea.TeamManager, idea.TeamVP, (int)idea.Status, idea.SubmitTime, idea.Approver, idea.ApproverComment, 
                idea.DcComment, idea.DcAttendMeeting, idea.SecretLevel, idea.CanSearch, 
                idea.FinalReviewTopicID, idea.FinalReviewID, idea.FinalAward, idea.FinalType,
                idea.FinalIP, idea.FinalAction, idea.FinalActionBU, (int)idea.FinalActionStatus,
                (int)idea.FinalIPStatus);
            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void DeleteIdea(int id)
        {
            DeleteIdea(id, DBHelper.GetDatabase(), null);

        }
        public static void DeleteIdea(int id, TranscationHelper trans)
        {
            DeleteIdea(id, trans.DataBase, trans.Transaction);
        }
        private static void DeleteIdea(int id, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_DeleteIdea", id);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, true);
            }
        }


        #endregion

        #region history

        public static List<IdeaHis> SearchIdeaHistory(int ideaID)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaHis", ideaID);

            List<IdeaHis> ideas = new List<IdeaHis>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdeaHis(dataReader));
                }
            }

            return ideas;
        }

        private static IdeaHis PopulateIdeaHis(IDataReader dataReader)
        {
            IdeaHis idea = new IdeaHis(dataReader.GetInt32(0));
            idea.Log = dataReader.GetString(1);
            idea.IdeaID = dataReader.GetInt32(2);
            idea.TopicID = dataReader.GetInt32(3);
            idea.No = dataReader.GetInt32(4);
            idea.Title = dataReader.GetString(5);
            idea.Type = dataReader.GetString(6);
            if (!dataReader.IsDBNull(7)) idea.RelatedTypeText = dataReader.GetString(7);
            if (!dataReader.IsDBNull(8)) idea.KeywordsText = dataReader.GetString(8);
            if (!dataReader.IsDBNull(9)) idea.FreewordsText = dataReader.GetString(9);
            idea.Backgroud = dataReader.GetString(10);
            idea.Description = dataReader.GetString(11);
            idea.Values = dataReader.GetString(12);
            idea.PptFileID = dataReader.IsDBNull(13) ? (Guid?)null : dataReader.GetGuid(13);
            idea.Pic1FileID = dataReader.IsDBNull(14) ? (Guid?)null : dataReader.GetGuid(14);
            idea.Pic2FileID = dataReader.IsDBNull(15) ? (Guid?)null : dataReader.GetGuid(15);
            idea.Pic3FileID = dataReader.IsDBNull(16) ? (Guid?)null : dataReader.GetGuid(16);
            idea.Owner = dataReader.GetString(17);
            idea.TeamID = dataReader.GetInt32(18);
            idea.TeamManager = dataReader.GetString(19);
            idea.TeamVP = dataReader.GetString(20);
            idea.Approver = dataReader.IsDBNull(21) ? null : dataReader.GetString(21);
            idea.ApproverComment = dataReader.IsDBNull(22) ? null : dataReader.GetString(22);

            return idea;
        }

        public static void InsertIdeaHis(Idea ideaHis, string log)
        {
            InsertIdeaHis(ideaHis, log, DBHelper.GetDatabase(), null);
        }
        public static void InsertIdeaHis(Idea ideaHis, string log, TranscationHelper trans)
        {
            InsertIdeaHis(ideaHis, log, trans.DataBase, trans.Transaction);
        }
        private static void InsertIdeaHis(Idea ideaHis, string log, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertIdeaHis", log, ideaHis.ID, 
                ideaHis.TopicID, ideaHis.No, ideaHis.Title, ideaHis.Type, ideaHis.RelatedTypeText, 
                ideaHis.KeywordsText, ideaHis.FreewordsText, ideaHis.Backgroud, ideaHis.Description, 
                ideaHis.Values, ideaHis.PptFileID, ideaHis.Pic1FileID, ideaHis.Pic2FileID, ideaHis.Pic3FileID,
                ideaHis.Owner, ideaHis.TeamID, ideaHis.TeamManager, ideaHis.TeamVP, ideaHis.Approver, ideaHis.ApproverComment);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        #endregion










        /*
        public static List<Idea> GetIdea(int? belongTopicID, int? scheduleTopicID, int? reviewTopicID,
    string title, string owner, int? teamID, string teamManager, string teamVP,
    string submitType, IdeaStatus? status, List<int> multiStatus, string finalType,
    string finalDcOwner, string finalIPOwner,
    string reviewNextAction, bool? reviewNextIP,
    string reiviewActionExecutor, string reviewIPExecutor,
    int? reviewActionStatus, int? reviewIPStatus,
    string reviewType)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdea",
                belongTopicID, scheduleTopicID, reviewTopicID,
                title, owner, teamID, teamManager, teamVP,
                submitType, (int?)status, InHelper.ConvertToInStrForSplit(multiStatus, false), finalType,
                finalDcOwner, finalIPOwner,
                reviewNextAction, reviewNextIP,
                reiviewActionExecutor, reviewIPExecutor,
                reviewActionStatus, reviewIPStatus, reviewType);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }




        public static List<Idea> GetIdeaForDcIP(int? belongTopicID, int? reviewTopicID,
            string title, string owner, int? teamID, string teamManager, string teamVP,
            string submitType, IdeaStatus? status, List<int> multiStatus,
            string dcOwner, int? dcConfirmed, bool dcProcessed,
            string ipOwner, int? ipConfirmed, bool ipProcessed)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaForDcIP",
                belongTopicID, reviewTopicID,
                title, owner, teamID, teamManager, teamVP,
                submitType, (int?)status, InHelper.ConvertToInStrForSplit(multiStatus, false),
                dcOwner, dcConfirmed, ipOwner, ipConfirmed);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(PopulateIdea(dataReader));
                }
            }

            return ideas;
        }
        */
    }
}
